package com.example.ckycode.dao;

import com.example.skycodes.bean.NewsBean;
import com.example.skycodes.util.DBConnection;
import com.example.skycodes.util.Time;
import com.sun.org.apache.xpath.internal.objects.XNull;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
////
public class NewsDao {
    DBConnection DBConn = new DBConnection();
    public List<NewsBean> getList( int page) {

        public List<NewsBean> getList(int page) {
            Connection conn = DBConn.getConn();
            String sql = "select * from news limit "+(page-1)*10+",10";
            String sql = "select * from news limit " + (page - 1) * 10 + ",10";
            List<NewsBean> list = new ArrayList<NewsBean>();
            try {
                Statement statement = conn.createStatement();
                ResultSet rs = statement.executeQuery(sql);
                while (rs.next()) {
                    NewsBean newsBean = new NewsBean();
                    newsBean.setNewsID(rs.getInt("NewsID"));
                    newsBean.setNewsTitle(rs.getString("NewsTitle"));
                    newsBean.setNewsContent(rs.getString("NewsContent"));
                    newsBean.setNewsTime(Time.getDate());
                    newsBean.setAdminName(rs.getString("AdminName"));
                    list.add(newsBean);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return list;
        }


        ResultSet rs = statement.executeQuery(sql);
        while (rs.next()) {
            NewsBean newsBean = new NewsBean();
            newsBean.setNewsID(rs.getInt("NewsID"));
            newsBean.setNewsTitle(rs.getString("NewsTitle"));
            newsBean.setNewsContent(rs.getString("NewsContent"));
            newsBean.setNewsTime(Time.getDate());
            newsBean.setAdminName(rs.getString("AdminName"));
            list.add(newsBean);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
        return list;
}

        /**
         * 更新功能
         *
         * @param news
         @@ -42,27 +44,27 @@
         public int add(NewsBean news) {
         Connection conn = DBConn.getConn();
         String sql = "insert into news values(null,?,?,?,?)";
         try {
         PreparedStatement pmst = conn.prepareStatement(sql);
         pmst.setString(1, news.getNewsTitle());
         pmst.setString(2, news.getNewsContent());
         pmst.setString(3, Time.getDate());
         pmst.setString(4, news.getAdminName());
         return pmst.executeUpdate();
         } catch (SQLException e) {
         e.printStackTrace();
         }
         return 0;//添加失败返回0
         }

         public int update(NewsBean news) {
         Connection conn = DBConn.getConn();
         String sql = "update news set NewsTitle=?,NewsContent=?,NewsTime=?,AdminName=? where NewsID=?";
         try {
         public int add(NewsBean news) {
         Connection conn = DBConn.getConn();
         String sql = "insert into news values(null,?,?,?,?)";
         try {
         PreparedStatement pmst = conn.prepareStatement(sql);
         pmst.setString(1, news.getNewsTitle());
         pmst.setString(2, news.getNewsContent());
         pmst.setString(3, Time.getDate());
         pmst.setString(4, news.getAdminName());
         return pmst.executeUpdate();
         } catch (SQLException e) {
         e.printStackTrace();
         }
         return 0;//添加失败返回0
         }

         public int update(NewsBean news) {
         Connection conn = DBConn.getConn();
         String sql = "update news set NewsTitle=?,NewsContent=?,NewsTime=?,AdminName=? where NewsID=?";
         try {
         PreparedStatement pmst = conn.prepareStatement(sql);
         pmst.setString(1, news.getNewsTitle());
         pmst.setString(2, news.getNewsContent());
         pmst.setString(3,  Time.getDate());
         pmst.setString(3, Time.getDate());
         pmst.setString(4, news.getAdminName());
         pmst.setInt(5, news.getNewsID());
         return pmst.executeUpdate();
         } catch (SQLException e) {
         e.printStackTrace();
         }
         return 0;//添加失败返回0
         }

         public int delete(int newsID) {
         Connection conn = DBConn.getConn();
         try {
         String sql = "delete from news where NewsID=?";
         PreparedStatement pmst = conn.prepareStatement(sql);
         pmst.setInt(1, newsID);
         return pmst.executeUpdate();
         } catch (SQLException e) {
         e.printStackTrace();
         }
         return 0;
         }

         /**
          * 统计新闻总条数
          *
          * @return
         */
        public int count(){
            public int count() {
                Connection conn = DBConn.getConn();
                try{
                    try {
                        String sql = "select count(*) from news";
                        Statement statement = conn.createStatement();
                        ResultSet rs = statement.executeQuery(sql);
                        if (rs.next()) {
                            return rs.getInt(1);//cout查询返回第一行数据 第一行第一列就是总条数
                        }
                    }catch (SQLException e){
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                    return 0;
                }

                public NewsBean getNewsByID(int id) {
                    try {
                        Connection conn = DBConn.getConn();
                        String sql = "select * from news where NewsID=?";
                        NewsBean newsBean = new NewsBean();
                        PreparedStatement pmst = conn.prepareStatement(sql);
                        pmst.setInt(1, id);
                        ResultSet rs = pmst.executeQuery();
                        if (rs.next()) {
                            newsBean.setNewsID(rs.getInt(1));
                            newsBean.setNewsTitle(rs.getString(2));
                            newsBean.setNewsContent(rs.getString(3));
                            newsBean.setNewsTime(rs.getString(4));
                            newsBean.setAdminName(rs.getString(5));
                            return newsBean;
                        }
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }

                    return null;
                }
            }

